﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace DAO
{
    public class DataConnection
    {
        SqlConnection conn;
        SqlCommand comm;     

        public DataConnection(string connectionSql)
        {
            try
            {
                if (!string.IsNullOrEmpty(connectionSql))
                {
                    ConnectionData(connectionSql);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public void ConnectionData(string connStr)
        {
            try
            {
                conn = new SqlConnection(connStr);
                comm = conn.CreateCommand();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public void OpenConnection()
        {
            try
            {
                if (conn != null)
                {
                    if (conn.State != ConnectionState.Open)
                    {
                        conn.Open();
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        private void CloseConnection()
        {
            try
            {
                if (conn != null)
                {
                    if (conn.State != ConnectionState.Closed)
                    {
                        conn.Close();
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public int ExecuteSql(string strSql)
        {
            try
            {
                comm.CommandType = CommandType.Text;
                comm.CommandText = strSql;
                OpenConnection();
                return comm.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                CloseConnection();
            }
        }

        public object ExecuteString(string strSql)
        {
            try
            {
                comm.CommandType = CommandType.Text;
                comm.CommandText = strSql;
                OpenConnection();
                return comm.ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                CloseConnection();
            }
        }

        public DataTable ExecuteDataTable(string strSql)
        {
            try
            {
                DataTable dtResult = new DataTable();
                comm.CommandType = CommandType.Text;
                comm.CommandText = strSql;
                OpenConnection();
                SqlDataAdapter sdAdapter = new SqlDataAdapter(comm);
                sdAdapter.Fill(dtResult);
                return dtResult;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                CloseConnection();
            }
        }

        public DataSet ExecuteDataSet(string strSql)
        {
            try
            {
                DataSet dsResult = new DataSet();
                comm.CommandText = strSql;
                comm.CommandType = CommandType.Text;
                OpenConnection();
                SqlDataAdapter sdAdapter = new SqlDataAdapter(comm);
                sdAdapter.Fill(dsResult);
                return dsResult;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                CloseConnection();
            }
        }

        public DataTable ExecuteDataTableByProcedure(string strPro)
        {
            try
            {
                OpenConnection();
                comm.CommandText = strPro;
                comm.CommandType = CommandType.StoredProcedure;
                DataTable dt = new DataTable();
                SqlDataAdapter adAdapter = new SqlDataAdapter(comm);
                adAdapter.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                CloseConnection();
            }
        }

        #region 带SqlParameter
        public object ExecuteString(SqlParameter[] pa,string spString)
        {
            try
            {
                OpenConnection();
                comm.CommandText = spString;
                comm.CommandType = CommandType.StoredProcedure;
                foreach (SqlParameter item in pa)
                {
                    comm.Parameters.Add(item);
                }
                return comm.ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                CloseConnection();
            }
        }

        public DataTable ExecuteDataTable(SqlParameter[] pa, string spString)
        {
            try
            {
                OpenConnection();
                comm.CommandText = spString;
                comm.CommandType = CommandType.StoredProcedure;
                foreach (SqlParameter item in pa)
                {
                    comm.Parameters.Add(item);
                }
                DataTable dtResult = new DataTable();
                SqlDataAdapter sdAdapter = new SqlDataAdapter(comm);
                sdAdapter.Fill(dtResult);
                return dtResult;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                CloseConnection();
            }
        }
        #endregion

    }
}
